import lumenn.utils
import dbinterface
import dbtypes

# *********************************************************************
class SQLBase(DBInterface):
	
	# -------------------------------------------------------------------
	def __init__(self, debug = False):
		self.debug	=	debug
		self.log		=	lumenn.utils.applog
		self.InitDB()
		
	# -------------------------------------------------------------------
	def Query(self, q):
		if self.debug:
			self.log.write('Query: %s' % q)
		return self.connection.execute(q)
		
	# -------------------------------------------------------------------
	def HasTable(self, app, tablename):
		q = 'SELECT * FROM %s LIMIT 1' % ('%s_%s' % (app, tablename) )
		
		try:
			self.Query(q)
		except:
			return False
		
		return True
		
	# -------------------------------------------------------------------
	def CreateTable(self, app, tablename, fields):
		q = 'CREATE TABLE %s(' % ('%s_%s' % (app, tablename) )
		
		fs = []
		
		for k, v in fields.iteritems():
			if v['type'] == PRIMARY_KEY:
				s = '%s INT PRIMARY KEY' % k
			elif v['type'] == INT:
				s = '%s INT' % k
			elif v['type'] == REAL:
				s = '%s REAL' % k
			else:	
				s = '%s TEXT' % k
			
			if v.has_key('unique'):
				s += ' UNIQUE'
			
			if v.has_key('primarykey'):
				s += ' PRIMARY KEY'
			
			if v.has_key('default'):
				s += ' DEFAULT ' + v['default']
			
			fs.append(s)
		
		q += ', '.join(fs) + ')'
		
		self.Query(q)

	# -------------------------------------------------------------------
	def DropTable(self, app, tablename):
		q = 'DROP TABLE %s' % ('%s_%s' % (app, tablename) )
		
		self.Query(q)
		
	# -------------------------------------------------------------------
	def Select(self, app, tablename, cond = None, fields = None):
		q = 'SELECT '
		
		if fields:
			q += fields
			fields = fields.split(',')
			fields = [f.strip() for f in fields]
		else:
			fields = registry.GetTableDef(app, tablename).keys()
			q += ', '.join(fields)
		
		q += ' FROM %s ' % ('%s_%s' % (app, tablename) )
		
		if cond:
			q += cond
		
		c = self.connection.cursor()
		c.execute(q)
		result = c.fetchall()
		
		if not result:
			return result
		
		rows = []
		
		for row in result:
			r = {}
			for i in range(0, len(fields) ):
				r[ f[i] ] = row[i]
			
			rows.append(r)
		return rows
		
	# -------------------------------------------------------------------
	def Insert(self, app, tablename, cond = None, fields = None):
		q = 'INSERT INTO %s VALUES(' % ('%s_%s' % (app, tablename) )
		
	# -------------------------------------------------------------------
	def Store(self, v, type):
		if type == INT:
			return int(v)
		elif type == REAL:
			return float(v)
		elif type == PICKLE:
			return pickle.dumps(v)
		elif type == DATETIME:
			return v.strftime("'%Y%m%d%H%M%S'")
			
		return "'%s'" % v
		
	# -------------------------------------------------------------------
	def Retrieve(self, type):
		if type == INT:
			return int(v)
		elif type == REAL:
			return float(v)
		elif type == PICKLE:
			return pickle.loads(v)
		elif type == DATETIME:
			return datetime.strptime('%Y%m%d%H%M%S')
			
		return str(v)
		
	# -------------------------------------------------------------------
	def InitDB(self):
		for k, v in registry.GetTables('lumenn'):
			if not self.HasTable('lumenn', k):
				self.CreateTable('lumenn', k, v)
		
		r = self.Select('lumenn', 'users', cond = "WHERE username = 'root'")
		
		if not r:
			self.Insert('lumenn', 'users', 
				{ 
					'username'	: 'root',
					'password'	: '\x8a\x99\x12Uv\xf6S\xd9\x82\xf5#\xccU\x06\x08\xb4',
				}
			)
